﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for ManagementContract
/// </summary>
public class ManagementContract
{
    SqlConnection coon;
	public ManagementContract()
	{
		//
		// TODO: Add constructor logic here
		//
        coon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
	}

    public bool OrderContract(int ServiceCategoryID, int SubCategoryID, int PaymentMethodID, string CustomerID)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string sql = "INSERT INTO [Contract](ServiceCategoryID,SubCategoryID,PaymentMethodID,CustomerID,TotalAmount,Status) VALUES(@ServiceCategoryID,@SubCategoryID,@PaymentMethodID,@CustomerID,@TotalPrice,'New')";
        string querySum = "SELECT (SecurityDeposit+PriceRental) FROM ServiceCategory "
                            +"JOIN SubCategory "
                            +"ON ServiceCategory.ServiceCategoryID = SubCategory.ServiceCategoryID "
                            +"JOIN PaymentMethod "
                            +"ON SubCategory.SubCategoryID = PaymentMethod.SubCategoryID "
                           +"WHERE PaymentMethod.PaymentMethodID ="+PaymentMethodID;

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = coon;
        cmd.CommandText = querySum;
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        double TotalPrice = dr.GetDouble(0);
        dr.Close();
        cmd.CommandText = sql;

        cmd.Parameters.AddWithValue("@ServiceCategoryID", ServiceCategoryID);
        cmd.Parameters.AddWithValue("@SubCategoryID", SubCategoryID);
        cmd.Parameters.AddWithValue("@PaymentMethodID", PaymentMethodID);
        cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
        cmd.Parameters.AddWithValue("@TotalPrice", TotalPrice);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }

    public DataSet GetNewContract()
    {
        string query = "SELECT ContractID,DateCreate,Contract.CustomerID,CustomerName,Phone,Email,Address,Status FROM Contract"
                        +" JOIN Customer"
                        +" ON Contract.CustomerID=Customer.CustomerID"
                        + " WHERE Status = 'New'";
        SqlDataAdapter da = new SqlDataAdapter(query,coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public bool NotFeasibility(int ContractID)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "UPDATE Contract SET Status = 'Cancel' WHERE ContractID = " + ContractID;
        SqlCommand cmd = new SqlCommand(query, coon);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }



    public DataSet LoadContract(int contractID)
    {
        string query = "SELECT Contract.TotalAmount,Customer.CustomerID,CustomerName,Phone,Email,Address,ServiceName,SecurityDeposit,SubName,PaymentName,PriceRental,Note,Customer.Password,Contract.DateActive,ContractID FROM Contract "
                        +"JOIN Customer "
                        +"ON Contract.CustomerID = Customer.CustomerID "
                        +"JOIN ServiceCategory "
                        +"ON Contract.ServiceCategoryID = ServiceCategory.ServiceCategoryID "
                        +"JOIN SubCategory "
                        +"ON Contract.SubCategoryID = SubCategory.SubCategoryID "
                        +"JOIN PaymentMethod "
                        +"ON Contract.PaymentMethodID = PaymentMethod.PaymentMethodID "
                        +"WHERE ContractID="+contractID;
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public bool FeasibilityContract(int p)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "UPDATE Contract SET Status = 'Feasibility' WHERE ContractID = " + p;
        SqlCommand cmd = new SqlCommand(query, coon);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }

    public DataSet GetContractFeasibility()
    {
        string query = "SELECT ContractID,DateCreate,Contract.CustomerID,CustomerName,Phone,Email,Status FROM Contract"
                        + " JOIN Customer"
                        + " ON Contract.CustomerID=Customer.CustomerID"
                        + " WHERE Status = 'Feasibility'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public bool ActiveContract(object p)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "UPDATE Contract SET DateActive = GETDATE(),Status = 'Active' WHERE ContractID = " + p;
        SqlCommand cmd = new SqlCommand(query, coon);
        int result = cmd.ExecuteNonQuery();
        coon.Close();
        return result > 0 ? true : false;
    }

    public DataSet GetAllContract()
    {
        string query = "SELECT * FROM Contract JOIN Customer ON Contract.CustomerID=Customer.CustomerID";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public DataSet SearchByContractID(string p)
    {
        string query = "SELECT * FROM Contract JOIN Customer ON Contract.CustomerID=Customer.CustomerID WHERE Contract.ContractID="+p;
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public DataSet SearchByCustomerID(string p)
    {
        string query = "SELECT * FROM Contract JOIN Customer ON Contract.CustomerID=Customer.CustomerID WHERE Contract.CustomerID='" + p+"'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public DataSet SearchByCustomerName(string p)
    {
        string query = "SELECT * FROM Contract JOIN Customer ON Contract.CustomerID=Customer.CustomerID WHERE  CustomerName like '%" + p + "%'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public int CountTotalContract(string p1, string p2)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "SELECT COUNT(*) FROM Contract WHERE DateCreate >= '" + p1 + "' AND DateCreate <= '" + p2 + "'";
        SqlCommand command = new SqlCommand(query, coon);
        SqlDataReader dr = command.ExecuteReader();
        dr.Read();
        int result = dr.GetInt32(0);
        dr.Close();
        return result;
    }

    public int CountActiveContract(string p1, string p2)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "SELECT COUNT(*) FROM Contract WHERE DateCreate >= '" + p1 + "' AND DateCreate <= '" + p2 + "' AND Status = 'Active'";
        SqlCommand command = new SqlCommand(query, coon);
        SqlDataReader dr = command.ExecuteReader();
        dr.Read();
        int result = dr.GetInt32(0);
        dr.Close();
        return result;
    }

    public int CountCancelContract(string p1, string p2)
    {
        if (coon.State == ConnectionState.Closed)
            coon.Open();
        string query = "SELECT COUNT(*) FROM Contract WHERE DateCreate >= '" + p1 + "' AND DateCreate <= '" + p2 + "' AND Status = 'Cancel'";
        SqlCommand command = new SqlCommand(query, coon);
        SqlDataReader dr = command.ExecuteReader();
        dr.Read();
        int result = dr.GetInt32(0);
        dr.Close();
        return result;
    }

    public DataSet GetContractByDate(string p1, string p2)
    {
        string query = "SELECT * FROM Contract JOIN Customer ON Contract.CustomerID=Customer.CustomerID WHERE DateCreate >= '" + p1 + "' AND DateCreate <= '" + p2 + "'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public DataSet GetContractActiveByDate(string p1, string p2)
    {
        string query = "SELECT * FROM Contract "
                        + "JOIN Customer "
                        + "ON Contract.CustomerID=Customer.CustomerID "
                        + "WHERE DateCreate >=   '" + p1 + "' AND DateCreate <= '" + p2 + "' AND Status = 'Active'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }

    public DataSet GetContracCanceltByDate(string p1, string p2)
    {
        string query = "SELECT * FROM Contract "
                        + "JOIN Customer "
                        + "ON Contract.CustomerID=Customer.CustomerID "
                        + "WHERE DateCreate >=   '" + p1 + "' AND DateCreate <= '" + p2 + "' AND Status = 'Cancel'";
        SqlDataAdapter da = new SqlDataAdapter(query, coon);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}